﻿using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using EzPos.Model.Common;
using EzPos.Model.SaleOrder;
using EzPos.Properties;
using EzPos.Service.Expense;
using EzPos.Service.SaleOrder;
using Microsoft.Office.Interop.Excel;
using ExcelApplication = Microsoft.Office.Interop.Excel.Application;

namespace EzPos.Service.Report
{
    public class ReportService
    {
        public SaleOrderService SaleOrderService { get; set; }
        public ExpenseService ExpenseService { get; set; }

        public string SaleStatementReport(string startDate, string endDate)
        {
            if (string.IsNullOrEmpty(startDate))
                return string.Empty;

            if (string.IsNullOrEmpty(endDate))
                return string.Empty;

            if (SaleOrderService == null)
                return string.Empty;

            //Sales
            var searchCriteria =
                new List<string>
                    {
                        //"SaleOrderId IN (SELECT SaleOrderId FROM TSaleOrders WHERE SaleOrderTypeId = 0)",
                        "SaleOrderId IN (SELECT SaleOrderId FROM TSaleOrders WHERE SaleOrderDate BETWEEN CONVERT(DATETIME, '" + startDate + "', 103) AND CONVERT(DATETIME, '" +
                        endDate + " 23:59', 103))"
                    };
            var saleOrderList = SaleOrderService.GetSaleOrders(searchCriteria);
            var saleItemList = SaleOrderService.GetSaleItems(searchCriteria);

            if ((saleOrderList == null) || (saleOrderList.Count == 0))
                return string.Empty;

            //Write to Excel file
            var templateReportFile =
                System.Windows.Forms.Application.StartupPath + @"\" +
                Resources.ConstSaleStatementExcelFile;

            var reportFileInfo = new FileInfo(templateReportFile);
            if (!reportFileInfo.Exists)
                return string.Empty;

            var invoicePeriode =
                startDate + " ដល់ " + endDate;

            //Open workbook                
            var excelApplication = new ExcelApplication();
            var workBook = excelApplication.Workbooks.Open(
                templateReportFile,
                0,
                false,
                5,
                false,
                string.Empty,
                false,
                XlPlatform.xlWindows,
                string.Empty,
                true,
                false,
                0,
                true,
                false,
                false);

            //Invoice content
            var workSheet = (Worksheet)workBook.Worksheets[Resources.ConstSheetSaleStatement];

            //Shop name
            var rowIndex = 1;
            var excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "របាយ​ការណ៏​លក់​ប្រចាំ​ថ្ងៃរបស់​ " + AppContext.ShopNameLocal;

            //Period
            rowIndex = 2;
            excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "ពី " + invoicePeriode;

            //Sale            
            rowIndex = 5;

            var grandTotalSoldForeign = 0f;
            var grandTotalSoldLocal = 0f;
            var grandTotalPaidForeign = 0f;
            var grandTotalPaidLocal = 0f;
            var grandTotalReturnForeign = 0f;
            var grandTotalReturnLocal = 0f;
            foreach (Model.SaleOrder.SaleOrder saleOrder in saleOrderList)
            {
                if (saleOrder == null)
                    continue;

                var localSaleOrder = saleOrder;

                var exchangeRate = saleOrder.ExchangeRate;

                var totalPaidForeign = saleOrder.AmountPaidForeign;
                var totalPaidLocal = saleOrder.AmountPaidLocal;

                var totalReturnForeign = saleOrder.AmountReturnForeign;
                var totalReturnLocal = saleOrder.AmountReturnLocal;

                var discountPercentage = saleOrder.Discount;

                var filteredSaleItemList =
                    (from saleOrderReport
                         in saleItemList.Cast<SaleItem>()
                     where saleOrderReport.SaleOrderId == localSaleOrder.SaleOrderId
                     select saleOrderReport).ToList();

                //Invoice number
                var invoiceNumber = saleOrder.SaleOrderNumber;
                invoiceNumber += 
                    string.IsNullOrEmpty(saleOrder.ReferenceNum) ? 
                    string.Empty : "(" + saleOrder.ReferenceNum + ")";
                workSheet.get_Range("A" + rowIndex + ":B" + rowIndex, Type.Missing).Merge(Type.Missing);
                excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = "លេខ​វិក្ក័យ​ប័ត្រ: " + invoiceNumber;

                //Customer
                workSheet.get_Range("D" + rowIndex + ":F" + rowIndex, Type.Missing).Merge(Type.Missing);
                excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                excelRange.Select();
                excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
                excelRange.Value2 = "កាល​បរិច្ឆេទ​: " + ((DateTime)saleOrder.SaleOrderDate).ToString("dd/MM/yyyy", AppContext.CultureInfo);

                excelRange = workSheet.get_Range("A" + rowIndex, "F" + rowIndex);                    
                excelRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
                excelRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
                excelRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
                excelRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;

                rowIndex += 1;
                excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                excelRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, 1);

                var orderNumber = 0;
                foreach (var saleItem in filteredSaleItemList.Where(saleItem => saleItem != null))
                {
                    orderNumber += 1;
                    excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = orderNumber;

                    excelRange = workSheet.get_Range("B" + rowIndex, "B" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = saleItem.FkProduct.Description;

                    var quantity = saleItem.QtySold;
                    excelRange = workSheet.get_Range("C" + rowIndex, "C" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = quantity;

                    var bonusQuantity = saleItem.QtyBonus;
                    excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = bonusQuantity;

                    var unitPrice = saleItem.UnitPriceOut;
                    excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = unitPrice;

                    var subTotal = quantity * unitPrice;
                    excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = subTotal;

                    excelRange = workSheet.get_Range("A" + rowIndex, "F" + rowIndex);
                    excelRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDot;

                    rowIndex += 1;
                    excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                    excelRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, 1);
                }

                //Total amount
                //Total Amount before discount
                var discountableTotalAmount =
                    (from saleItem
                     in saleItemList.Cast<SaleItem>()
                     where
                        (saleItem.SaleOrderId == localSaleOrder.SaleOrderId)
                        && (saleItem.QtyBonus == 0)
                        && (saleItem.FkProduct != null)
                        && ((from categoryId in AppContext.DiscountProductCategoryList select categoryId).Contains(saleItem.FkProduct.CategoryId))
                     select (saleItem.QtySold * saleItem.UnitPriceOut)).Sum();

                var nonDiscountableTotalAmount =
                    (from saleItem
                    in saleItemList.Cast<SaleItem>()
                     where
                         (saleItem.SaleOrderId == localSaleOrder.SaleOrderId)
                         && (saleItem.FkProduct != null)
                         && ((saleItem.QtyBonus != 0)
                         || !((from categoryId in AppContext.DiscountProductCategoryList select categoryId).Contains(saleItem.FkProduct.CategoryId)))
                     select (saleItem.QtySold * saleItem.UnitPriceOut)).Sum();

                var totalSoldLocal = discountableTotalAmount + nonDiscountableTotalAmount;
                var totalSoldForeign = totalSoldLocal/exchangeRate;

                //Discount
                var discountAmountLocal = (discountableTotalAmount * discountPercentage) / 100;
                var discountAmountForeign = discountAmountLocal / exchangeRate;

                //Total amount after discount
                var totalAmountLocal = totalSoldLocal - discountAmountLocal;
                var totalAmountForeign = totalSoldForeign - discountAmountForeign;

                //SubTotal
                excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = "សរុប";
                excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
                excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalSoldLocal;
                excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalSoldForeign;

                //Discount
                rowIndex += 1;
                excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = "បញ្ចុះ​តំលៃ " + saleOrder.Discount + " %";
                excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
                excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                excelRange.Select();

                excelRange.Value2 = discountAmountLocal;
                excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = discountAmountForeign;

                //Grand Total
                rowIndex += 1;
                excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = "ប្រាក់​សរុប";
                excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
                excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalAmountLocal;
                excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalAmountForeign;

                //Paid
                rowIndex += 1;
                excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = "ប្រាក់​ទទួល";
                excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
                excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalPaidLocal;
                excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalPaidForeign;

                //Return
                rowIndex += 1;
                excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = "ប្រាក់​អាប់";
                excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
                excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalReturnLocal;
                excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = totalReturnForeign;

                rowIndex += 2;

                grandTotalSoldLocal += totalAmountLocal;
                grandTotalSoldForeign += totalAmountForeign;

                grandTotalPaidLocal += totalPaidLocal;
                grandTotalPaidForeign += totalPaidForeign;

                grandTotalReturnLocal += totalReturnLocal;
                grandTotalReturnForeign += totalReturnForeign;
            }

            //rowIndex += 2;
            excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "សរុប​ប្រាក់​លក់";
            excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
            excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalSoldLocal;
            excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalSoldForeign;
            excelRange = workSheet.get_Range("E" + rowIndex, "F" + rowIndex);
            excelRange.Font.Bold = true;
            excelRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(253, 233, 217));

            rowIndex += 1;
            excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "សរុប​ប្រាក់ទទួល";
            excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
            excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalPaidLocal;
            excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalPaidForeign;
            excelRange = workSheet.get_Range("E" + rowIndex, "F" + rowIndex);
            excelRange.Font.Bold = true;
            excelRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(253, 233, 217));

            rowIndex += 1;
            excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "សរុប​ប្រាក់អាប់";
            excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
            excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalReturnLocal;
            excelRange = workSheet.get_Range("F" + rowIndex, "F" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalReturnForeign;
            excelRange = workSheet.get_Range("E" + rowIndex, "F" + rowIndex);
            excelRange.Font.Bold = true;
            excelRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(253, 233, 217));
                
            excelRange = workSheet.get_Range("A1", "A1");
            excelRange.Select();

            var reportFileName =
                System.Windows.Forms.Application.StartupPath + @"\" +
                DateTime.Now.Ticks + " - " +
                Resources.ConstSaleStatementExcelFile;

            workBook.Close(
                true,
                reportFileName,
                System.Reflection.Missing.Value);

            excelApplication.Quit();

            Marshal.ReleaseComObject(excelApplication);
            GC.Collect(0, GCCollectionMode.Forced);
            GC.WaitForPendingFinalizers();

            return reportFileName;
        }

        public string ExpenseStatementReport(string startDate, string endDate)
        {
            if (string.IsNullOrEmpty(startDate))
                return string.Empty;

            if (string.IsNullOrEmpty(endDate))
                return string.Empty;

            if (ExpenseService == null)
                return string.Empty;

            //Expenses
            var searchCriteria =
                new List<string>
                    {
                        "CONVERT(DATETIME, ExpenseDate, 103) BETWEEN CONVERT(DATETIME, '" + startDate + "', 103) AND CONVERT(DATETIME, '" + endDate + " 23:59', 103)"
                    };
            var expenseList = ExpenseService.GetExpenses(searchCriteria);

            if ((expenseList == null) || (expenseList.Count == 0))
                return string.Empty;

            //Write to Excel file
            var templateReportFile =
                System.Windows.Forms.Application.StartupPath + @"\" +
                Resources.ConstExpenseStatementExcelFile;

            var reportFileInfo = new FileInfo(templateReportFile);
            if (!reportFileInfo.Exists)
                return string.Empty;

            var expensePeriod =
                startDate + " ដល់ " + endDate;

            //Open workbook                
            var excelApplication = new ExcelApplication();
            var workBook = excelApplication.Workbooks.Open(
                templateReportFile,
                0,
                false,
                5,
                false,
                string.Empty,
                false,
                XlPlatform.xlWindows,
                string.Empty,
                true,
                false,
                0,
                true,
                false,
                false);

            //Expense content
            var workSheet = (Worksheet)workBook.Worksheets[Resources.ConstSheetExpenseStatement];

            //Shop name
            var rowIndex = 1;
            var excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "របាយ​ការណ៏​ចំណាយ​​ប្រចាំ​ថ្ងៃរបស់​ " + AppContext.ShopNameLocal;

            //Period
            rowIndex = 2;
            excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "ពី " + expensePeriod;

            //Expense            
            rowIndex = 5;
            var grandTotalExpenseForeign = 0f;
            var grandTotalExpenseLocal = 0f;

            var expenseDateList =
                (from expense
                in expenseList.Cast<Model.Expense.Expense>()
                select ((DateTime) expense.ExpenseDate).Date).Distinct().ToList();

            foreach (var expenseDate in expenseDateList)
            {
                var localExpenseDate = expenseDate;
                var filteredExpenseList =
                    (from anExpense
                    in expenseList.Cast<Model.Expense.Expense>()
                    where
                        ((DateTime)anExpense.ExpenseDate).Date == localExpenseDate
                    select anExpense).ToList();

                //Expense date
                workSheet.get_Range("A" + rowIndex + ":E" + rowIndex, Type.Missing).Merge(Type.Missing);
                excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = expenseDate.ToString("dd/MM/yyyy", AppContext.CultureInfo);                

                excelRange = workSheet.get_Range("A" + rowIndex, "E" + rowIndex);
                excelRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
                excelRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
                excelRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
                excelRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;

                rowIndex += 1;
                excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                excelRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, 1);

                var subTotalExpenseForeign = 0f;
                var subTotalExpenseLocal = 0f;
                var orderNumber = 0;
                foreach (var anExpense in filteredExpenseList)
                {
                    if (anExpense == null)
                        continue;

                    orderNumber += 1;
                    excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = orderNumber;

                    excelRange = workSheet.get_Range("B" + rowIndex, "B" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = anExpense.ExpenseTypeStr;

                    var quantity = anExpense.Description;
                    excelRange = workSheet.get_Range("C" + rowIndex, "C" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = quantity;

                    var expenseAmountLocal = anExpense.ExpenseAmountRiel;
                    excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = expenseAmountLocal;

                    var expenseAmountForeign = anExpense.ExpenseAmountInt;
                    excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                    excelRange.Select();
                    excelRange.Value2 = expenseAmountForeign;

                    subTotalExpenseLocal += expenseAmountLocal;
                    subTotalExpenseForeign += expenseAmountForeign;

                    rowIndex += 1;
                    excelRange = workSheet.get_Range("A" + rowIndex, "A" + rowIndex);
                    excelRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, 1);
                }

                //SubTotal
                excelRange = workSheet.get_Range("C" + rowIndex, "C" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = "សរុប";
                excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
                excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = subTotalExpenseLocal;
                excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
                excelRange.Select();
                excelRange.Value2 = subTotalExpenseForeign;

                //Grand Total
                rowIndex += 2;

                grandTotalExpenseLocal += subTotalExpenseLocal;
                grandTotalExpenseForeign += subTotalExpenseForeign;
            }

            //rowIndex += 2;
            excelRange = workSheet.get_Range("C" + rowIndex, "C" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = "សរុប​ប្រាក់ចំណាយ";
            excelRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
            excelRange = workSheet.get_Range("D" + rowIndex, "D" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalExpenseLocal;
            excelRange = workSheet.get_Range("E" + rowIndex, "E" + rowIndex);
            excelRange.Select();
            excelRange.Value2 = grandTotalExpenseForeign;
            excelRange = workSheet.get_Range("D" + rowIndex, "E" + rowIndex);
            excelRange.Font.Bold = true;
            excelRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(253, 233, 217));

            excelRange = workSheet.get_Range("A1", "A1");
            excelRange.Select();

            var reportFileName =
                System.Windows.Forms.Application.StartupPath + @"\" +
                DateTime.Now.Ticks + " - " +
                Resources.ConstExpenseStatementExcelFile;

            workBook.Close(
                true,
                reportFileName,
                System.Reflection.Missing.Value);

            excelApplication.Quit();

            Marshal.ReleaseComObject(excelApplication);
            GC.Collect(0, GCCollectionMode.Forced);
            GC.WaitForPendingFinalizers();

            return reportFileName;
        }
    }
}